﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;

/// <summary>
/// 文件名：ClsOutAdjustDAL.cs
/// 作  者：lihy/SAM
/// 时  间：2006.12.17/2007.01.30
/// 说  明：ClsOutAdjustDAL类用于出库调整信息相关处理
/// </summary>
public class ClsOutAdjustDAL
{
    /// <summary>
    /// 数据库连接字符串
    /// </summary>
    private static string strSqlConnectionString = ConfigurationManager.AppSettings["SQLCONNECTIONSTRING"];

    //获取所有出库调整主表信息
    public DataSet GetSellContractDataSet(string UserID, int Type)
    {
        DataSet dst = new DataSet();
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlDataAdapter dat = new SqlDataAdapter("Pro_GetOutAdjustDataSet", con);

        dat.SelectCommand.CommandType = CommandType.StoredProcedure;
        dat.SelectCommand.Parameters.Add("@u_log_id", SqlDbType.NVarChar, 50);
        dat.SelectCommand.Parameters["@u_log_id"].Value = UserID;
        dat.SelectCommand.Parameters.Add("@type", SqlDbType.Int, 4);
        dat.SelectCommand.Parameters["@type"].Value = Type;

        try
        {
            dat.Fill(dst);
        }
        catch
        {
            dst = null;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }

        return dst;
    }

    /// <summary>
    /// 新增或修改出库调整主表记录
    /// </summary>
    public Boolean AddOrUpdateSellContract(string Cm_No, DateTime Cm_Date, DateTime Cm_Mdate, string Ct_Id,  string Dp_Id, string Wk_Id, string Oper, string Remark, int OperateType)
    {
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlCommand com = new SqlCommand("Pro_AddOrUpdateOutAdjustM", con);

        com.CommandType = CommandType.StoredProcedure;
        com.Parameters.Add("@cm_no", SqlDbType.NVarChar, 30);
        com.Parameters["@cm_no"].Value = Cm_No;
        com.Parameters.Add("@cm_date", SqlDbType.DateTime, 8);
        com.Parameters["@cm_date"].Value = Cm_Date;
        com.Parameters.Add("@cm_mdate", SqlDbType.DateTime, 8);
        com.Parameters["@cm_mdate"].Value = Cm_Mdate;
        com.Parameters.Add("@ct_id", SqlDbType.NVarChar, 20);
        com.Parameters["@ct_id"].Value = Ct_Id;
        com.Parameters.Add("@dp_id", SqlDbType.NVarChar, 20);
        com.Parameters["@dp_id"].Value = Dp_Id;
        com.Parameters.Add("@wk_id", SqlDbType.NVarChar, 20);
        com.Parameters["@wk_id"].Value = Wk_Id;
        com.Parameters.Add("@oper", SqlDbType.NVarChar, 60);
        com.Parameters["@oper"].Value = Oper;
        com.Parameters.Add("@remark", SqlDbType.NVarChar, 255);
        com.Parameters["@remark"].Value = Remark;
        com.Parameters.Add("@operate_type", SqlDbType.Int, 4);
        com.Parameters["@operate_type"].Value = OperateType;

        try
        {
            con.Open();
            com.ExecuteNonQuery();

            return true;
        }
        catch
        {
            return false;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }
    }

    /// <summary>
    /// 改变合同订单的状态
    /// </summary>
    public Boolean UpdateSellContractState(string Cm_No, int State, string Auditor)
    {
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlCommand com = new SqlCommand("Pro_UpdateOutAdjustState", con);

        com.CommandType = CommandType.StoredProcedure;
        com.Parameters.Add("@cm_no", SqlDbType.NVarChar, 30);
        com.Parameters["@cm_no"].Value = Cm_No;
        com.Parameters.Add("@cm_state", SqlDbType.Int, 4);
        com.Parameters["@cm_state"].Value = State;
        com.Parameters.Add("@auditor", SqlDbType.NVarChar, 60);
        com.Parameters["@auditor"].Value = Auditor;

        try
        {
            con.Open();
            com.ExecuteNonQuery();

            return true;
        }
        catch
        {
            return false;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }
    }

    //获取所有出库调整从表信息
    public DataSet GetSellContractDataSetChild(string Cm_No, int Type)
    {
        DataSet dst = new DataSet();
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlDataAdapter dat = new SqlDataAdapter("Pro_GetOutAdjustDataSetChild", con);

        dat.SelectCommand.CommandType = CommandType.StoredProcedure;
        dat.SelectCommand.Parameters.Add("@cm_no", SqlDbType.NVarChar, 30);
        dat.SelectCommand.Parameters["@cm_no"].Value = Cm_No;
        dat.SelectCommand.Parameters.Add("@type", SqlDbType.Int, 4);
        dat.SelectCommand.Parameters["@type"].Value = Type;

        try
        {
            dat.Fill(dst);
        }
        catch
        {
            dst = null;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }

        return dst;
    }

    //新增或修改出库调整从表记录
    public Boolean AddOrUpdateSellContracChild(int Index, string Cm_No, string Im_No,  decimal Agio, string Remark, int OperateType)
    {
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlCommand com = new SqlCommand("Pro_AddOrUpdateOutAdjustChild", con);

        com.CommandType = CommandType.StoredProcedure;
        com.Parameters.Add("@index", SqlDbType.Int, 4);
        com.Parameters["@index"].Value = Index;
        com.Parameters.Add("@cm_no", SqlDbType.NVarChar, 30);
        com.Parameters["@cm_no"].Value = Cm_No;
        com.Parameters.Add("@im_no", SqlDbType.NVarChar, 30);
        com.Parameters["@im_no"].Value = Im_No;
        //com.Parameters.Add("@qty", SqlDbType.Decimal, 17);
        //com.Parameters["@qty"].Value = Qty;
        //com.Parameters.Add("@qty1", SqlDbType.Decimal, 17);
        //com.Parameters["@qty1"].Value = Qty1;
        com.Parameters.Add("@price", SqlDbType.Decimal, 28);
        com.Parameters["@price"].Value = Agio;
        com.Parameters.Add("@remark", SqlDbType.NVarChar, 255);
        com.Parameters["@remark"].Value = Remark;
        com.Parameters.Add("@operate_type", SqlDbType.Int, 4);
        com.Parameters["@operate_type"].Value = OperateType;

        try
        {
            con.Open();
            com.ExecuteNonQuery();

            return true;
        }
        catch
        {
            return false;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }
    }

    //判断同一合同订单中是否存在同类商品
    public int SellContractChildExistSameMateriel(string Cm_No, string Im_No)
    {
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlCommand com = new SqlCommand("Pro_SellContractChildExistSameMateriel", con);

        com.CommandType = CommandType.StoredProcedure;
        com.Parameters.Add("@cm_no", SqlDbType.NVarChar, 30);
        com.Parameters["@cm_no"].Value = Cm_No;
        com.Parameters.Add("@im_no", SqlDbType.NVarChar, 30);
        com.Parameters["@im_no"].Value = Im_No;

        try
        {
            con.Open();
            return Int32.Parse(com.ExecuteScalar().ToString());
        }
        catch
        {
            return -1;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }
    }
   
    //改变合同订单从表的状态
    public Boolean UpdateSellContractStateChild(int Index, int State)
    {
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlCommand com = new SqlCommand("Pro_UpdateOutAdjustStateChild", con);

        com.CommandType = CommandType.StoredProcedure;
        com.Parameters.Add("@index", SqlDbType.Int, 4);
        com.Parameters["@index"].Value = Index;
        com.Parameters.Add("@state", SqlDbType.Int, 4);
        com.Parameters["@state"].Value = State;

        try
        {
            con.Open();
            com.ExecuteNonQuery();

            return true;
        }
        catch
        {
            return false;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }
    }

    //获取所有销售发货主表信息
    public DataSet GetSellConsignmentDataSet(string Dm_Type, int Type)
    {
        DataSet dst = new DataSet();
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlDataAdapter dat = new SqlDataAdapter("Pro_GetSellConsignmentDataSet", con);

        dat.SelectCommand.CommandType = CommandType.StoredProcedure;
        dat.SelectCommand.Parameters.Add("@dm_type", SqlDbType.NVarChar, 2);
        dat.SelectCommand.Parameters["@dm_type"].Value = Dm_Type;
        dat.SelectCommand.Parameters.Add("@type", SqlDbType.Int, 4);
        dat.SelectCommand.Parameters["@type"].Value = Type;

        try
        {
            dat.Fill(dst);
        }
        catch
        {
            dst = null;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }

        return dst;
    }

    //新增或修改销售发货主表记录
    public Boolean AddOrUpdateSellConsignment(string Dm_Type, string Dm_No, DateTime Dm_Date, string Cm_No, string Cm_Date, DateTime Cm_Mdate, string Ct_Id, string Accounts, string Linkman, string Tel, string Fax, string Email, string Cm_Addr, string Postcode, string Sm_Id, string Account_Date, string Dp_Id, string Wk_Id, string Oper, string Remark, int OperateType)
    {
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlCommand com = new SqlCommand("Pro_AddOrUpdateSellConsignment", con);

        com.CommandType = CommandType.StoredProcedure;
        com.Parameters.Add("@dm_type", SqlDbType.NVarChar, 2);
        com.Parameters["@dm_type"].Value = Dm_Type;
        com.Parameters.Add("@dm_no", SqlDbType.NVarChar, 30);
        com.Parameters["@dm_no"].Value = Dm_No;
        com.Parameters.Add("@dm_date", SqlDbType.DateTime, 8);
        com.Parameters["@dm_date"].Value = Dm_Date;
        com.Parameters.Add("@cm_no", SqlDbType.NVarChar, 30);
        com.Parameters["@cm_no"].Value = Cm_No;
        com.Parameters.Add("@cm_date", SqlDbType.NVarChar, 20);
        com.Parameters["@cm_date"].Value = Cm_Date;
        com.Parameters.Add("@cm_mdate", SqlDbType.DateTime, 8);
        com.Parameters["@cm_mdate"].Value = Cm_Mdate;
        com.Parameters.Add("@ct_id", SqlDbType.NVarChar, 20);
        com.Parameters["@ct_id"].Value = Ct_Id;
        com.Parameters.Add("@acc", SqlDbType.NVarChar, 60);
        com.Parameters["@acc"].Value = Accounts;
        com.Parameters.Add("@li_man", SqlDbType.NVarChar, 60);
        com.Parameters["@li_man"].Value = Linkman;
        com.Parameters.Add("@tel", SqlDbType.NVarChar, 30);
        com.Parameters["@tel"].Value = Tel;
        com.Parameters.Add("@fax", SqlDbType.NVarChar, 20);
        com.Parameters["@fax"].Value = Fax;
        com.Parameters.Add("@email", SqlDbType.NVarChar, 60);
        com.Parameters["@email"].Value = Email;
        com.Parameters.Add("@cm_add", SqlDbType.NVarChar, 255);
        com.Parameters["@cm_add"].Value = Cm_Addr;
        com.Parameters.Add("@pos", SqlDbType.NVarChar, 20);
        com.Parameters["@pos"].Value = Postcode;
        com.Parameters.Add("@sm_id", SqlDbType.NVarChar, 20);
        com.Parameters["@sm_id"].Value = Sm_Id;
        com.Parameters.Add("@acc_date", SqlDbType.NVarChar, 30);
        com.Parameters["@acc_date"].Value = Account_Date;
        com.Parameters.Add("@dp_id", SqlDbType.NVarChar, 20);
        com.Parameters["@dp_id"].Value = Dp_Id;
        com.Parameters.Add("@wk_id", SqlDbType.NVarChar, 20);
        com.Parameters["@wk_id"].Value = Wk_Id;
        com.Parameters.Add("@oper", SqlDbType.NVarChar, 60);
        com.Parameters["@oper"].Value = Oper;
        com.Parameters.Add("@remark", SqlDbType.NVarChar, 255);
        com.Parameters["@remark"].Value = Remark;
        com.Parameters.Add("@operate_type", SqlDbType.Int, 4);
        com.Parameters["@operate_type"].Value = OperateType;

        try
        {
            con.Open();
            com.ExecuteNonQuery();

            return true;
        }
        catch
        {
            return false;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }
    }

    //作废销售发货主单
    public Boolean DeleteSellConsignment(string Dm_Type, string Dm_No, string Cm_No, int State)
    {
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlCommand com = new SqlCommand("Pro_DeleteSellConsignment", con);

        com.CommandType = CommandType.StoredProcedure;
        com.Parameters.Add("@dm_type", SqlDbType.NVarChar, 2);
        com.Parameters["@dm_type"].Value = Dm_Type;
        com.Parameters.Add("@dm_no", SqlDbType.NVarChar, 30);
        com.Parameters["@dm_no"].Value = Dm_No;
        com.Parameters.Add("@cm_no", SqlDbType.NVarChar, 30);
        com.Parameters["@cm_no"].Value = Cm_No;
        com.Parameters.Add("@state", SqlDbType.Int, 4);
        com.Parameters["@state"].Value = State;

        try
        {
            con.Open();
            com.ExecuteNonQuery();

            return true;
        }
        catch
        {
            return false;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }
    }

    //获取所有销售发货从表信息
    public DataSet GetSellConsignmentDataSetChild(string Dd_Type, string Dm_No)
    {
        DataSet dst = new DataSet();
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlDataAdapter dat = new SqlDataAdapter("Pro_GetSellConsignmentDataSetChild", con);

        dat.SelectCommand.CommandType = CommandType.StoredProcedure;
        dat.SelectCommand.Parameters.Add("@dd_type", SqlDbType.NVarChar, 2);
        dat.SelectCommand.Parameters["@dd_type"].Value = Dd_Type;
        dat.SelectCommand.Parameters.Add("@dm_no", SqlDbType.NVarChar, 30);
        dat.SelectCommand.Parameters["@dm_no"].Value = Dm_No;

        try
        {
            dat.Fill(dst);
        }
        catch
        {
            dst = null;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }

        return dst;
    }

    //改变销售发货从表的状态
    public Boolean UpdateSellConsignmentStateChild(string Dd_Type, string Dm_No, string Im_No, int State)
    {
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlCommand com = new SqlCommand("Pro_UpdateSellConsignmentStateChild", con);

        com.CommandType = CommandType.StoredProcedure;
        com.Parameters.Add("@dd_type", SqlDbType.NVarChar, 2);
        com.Parameters["@dd_type"].Value = Dd_Type;
        com.Parameters.Add("@dm_no", SqlDbType.NVarChar, 30);
        com.Parameters["@dm_no"].Value = Dm_No;
        com.Parameters.Add("@im_no", SqlDbType.NVarChar, 30);
        com.Parameters["@im_no"].Value = Im_No;
        com.Parameters.Add("@state", SqlDbType.Int, 4);
        com.Parameters["@state"].Value = State;

        try
        {
            con.Open();
            com.ExecuteNonQuery();

            return true;
        }
        catch
        {
            return false;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }
    }

    //修改销售发货从表信息
    public Boolean UpdateSellConsignmentInfoChild(string Dd_Type, string Dm_No, string Im_No, decimal Qty, string Remark)
    {
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlCommand com = new SqlCommand("Pro_UpdateSellConsignmentInfoChild", con);

        com.CommandType = CommandType.StoredProcedure;
        com.Parameters.Add("@dd_type", SqlDbType.NVarChar, 2);
        com.Parameters["@dd_type"].Value = Dd_Type;
        com.Parameters.Add("@dm_no", SqlDbType.NVarChar, 30);
        com.Parameters["@dm_no"].Value = Dm_No;
        com.Parameters.Add("@im_no", SqlDbType.NVarChar, 30);
        com.Parameters["@im_no"].Value = Im_No;
        com.Parameters.Add("@qty", SqlDbType.Decimal, 28);
        com.Parameters["@qty"].Value = Qty;
        com.Parameters.Add("@remark", SqlDbType.NVarChar, 255);
        com.Parameters["@remark"].Value = Remark;


        try
        {
            con.Open();
            com.ExecuteNonQuery();

            return true;
        }
        catch
        {
            return false;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }
    }

    //获取出货确认主表记录集
    public DataSet GetSellOutAffirmDataSet(int Type)
    {
        DataSet dst = new DataSet();
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlDataAdapter dat = new SqlDataAdapter("Pro_GetSellOutAffirmDataSet", con);

        dat.SelectCommand.CommandType = CommandType.StoredProcedure;
        dat.SelectCommand.Parameters.Add("@type", SqlDbType.Int, 4);
        dat.SelectCommand.Parameters["@type"].Value = Type;

        try
        {
            dat.Fill(dst);
        }
        catch
        {
            dst = null;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }

        return dst;
    }

    /// <summary>
    /// 获取出货确认从表记录集
    /// </summary>
    /// <param name="Dm_No">发货单号</param>
    /// <returns>返回数据集</returns>
    public DataSet GetSellOutAffirmDataSetChild(string Dm_No)
    {
        DataSet dst = new DataSet();
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlDataAdapter dat = new SqlDataAdapter("Pro_GetSellOutAffirmDataSetChild", con);

        dat.SelectCommand.CommandType = CommandType.StoredProcedure;
        dat.SelectCommand.Parameters.Add("@dm_no", SqlDbType.NVarChar, 30);
        dat.SelectCommand.Parameters["@dm_no"].Value = Dm_No;

        try
        {
            dat.Fill(dst);
        }
        catch
        {
            dst = null;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }

        return dst;
    }

    /// <summary>
    /// 确认出货确认
    /// </summary>
    /// <param name="Out_No">出货单号</param>
    /// 
    /// <param name="Type">类型(0确认,1取消确认)</param>
    /// <returns>操作是否成功</returns>
    public Boolean AuditSellOutAffirm(string Out_No, string Auditor, int Type)
    {
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlCommand com = new SqlCommand("Pro_AuditSellOutAffirm", con);

        com.CommandType = CommandType.StoredProcedure;
        com.Parameters.Add("@out_no", SqlDbType.NVarChar, 30);
        com.Parameters["@out_no"].Value = Out_No;
        com.Parameters.Add("@auditor",SqlDbType.NVarChar, 60);
        com.Parameters["@auditor"].Value = Auditor;
        com.Parameters.Add("@type", SqlDbType.Int, 4);
        com.Parameters["@type"].Value = Type;

        try
        {
            con.Open();
            com.ExecuteNonQuery();

            return true;
        }
        catch
        {
            return false;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }
    }

    /// <summary>
    ///  修改出货确认日期和备注
    /// </summary>
    /// <param name="Out_No">出货单号</param>
    /// <param name="Out_Date">出货日期</param>
    /// <param name="Remark">备注</param>
    /// <returns>操作是否成功</returns>
    public Boolean UpdateSellOutAffirm(string Out_No, DateTime Out_Date, string Remark)
    {
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlCommand com = new SqlCommand("Pro_UpdateSellOutAffirm", con);

        com.CommandType = CommandType.StoredProcedure;
        com.Parameters.Add("@out_no", SqlDbType.NVarChar, 30);
        com.Parameters["@out_no"].Value = Out_No;
        com.Parameters.Add("@out_date", SqlDbType.DateTime, 8);
        com.Parameters["@out_date"].Value = Out_Date;
        com.Parameters.Add("@remark", SqlDbType.NVarChar, 255);
        com.Parameters["@remark"].Value = Remark;

        try
        {
            con.Open();
            com.ExecuteNonQuery();

            return true;
        }
        catch
        {
            return false;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }
    }

    /// <summary>
    /// 判断出货确认确认状态
    /// </summary>
    /// <param name="Dm_No">发货单号</param>
    /// <returns>返回False未确认,True已确认</returns>
    public Boolean SellOutAffirmAuditState(string Dm_No)
    {
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlCommand com = new SqlCommand("Pro_SellOutAffirmAuditState", con);

        com.CommandType = CommandType.StoredProcedure;
        com.Parameters.Add("@dm_no", SqlDbType.NVarChar, 30);
        com.Parameters["@dm_no"].Value = Dm_No;

        try
        {
            con.Open();
            return Boolean.Parse(com.ExecuteScalar().ToString());
        }
        catch
        {
            return true;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }
    }

    /// <summary>
    /// 判断合同订单存在状况
    /// </summary>
    /// <param name="Cm_No">合同订单号</param>
    /// <param name="Type">类型(1是否存在发货,2是否存在子单)</param>
    /// <returns>返回存在数量</returns>
    public int SellContractExistState(string Cm_No, int Type)
    {
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlCommand com = new SqlCommand("Pro_OutAdjustExistState", con);

        com.CommandType = CommandType.StoredProcedure;
        com.Parameters.Add("@cm_no", SqlDbType.NVarChar, 30);
        com.Parameters["@cm_no"].Value = Cm_No;
        com.Parameters.Add("@type", SqlDbType.Int, 4);
        com.Parameters["@type"].Value = Type;

        try
        {
            con.Open();
            return Int32.Parse(com.ExecuteScalar().ToString());
        }
        catch
        {
            return -1;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }
    }

    /// <summary>
    /// 判断出货确认下的销售发票是否存在
    /// </summary>
    /// <param name="Cm_No">出货单号</param>
    /// <param name="Type">类型(1为销售发票)</param>
    /// <returns>返回存在数量</returns>
    public int InvoiceExistState(string Out_No, string Type)
    {
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlCommand com = new SqlCommand("Pro_InvoiceExistState", con);

        com.CommandType = CommandType.StoredProcedure;
        com.Parameters.Add("@out_no", SqlDbType.NVarChar, 20);
        com.Parameters["@out_no"].Value = Out_No;
        com.Parameters.Add("@type", SqlDbType.NVarChar, 2);
        com.Parameters["@type"].Value = Type;

        try
        {
            con.Open();
            return Int32.Parse(com.ExecuteScalar().ToString());
        }
        catch
        {
            return -1;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }
    }

    /// <summary>
    /// 获取未发货的商品数量
    /// </summary>
    /// <param name="Cm_No">订单号</param>
    /// <param name="Im_No">料号</param>
    /// <returns>返回未发货的商品数量</returns>
    public decimal GetSellContractChildRsSpareQty(string Cm_No, string Im_No)
    {
        SqlConnection con = new SqlConnection(strSqlConnectionString);
        SqlCommand com = new SqlCommand("Pro_GetSellContractChildRsSpareQty", con);

        com.CommandType = CommandType.StoredProcedure;
        com.Parameters.Add("@cm_no", SqlDbType.NVarChar, 30);
        com.Parameters["@cm_no"].Value = Cm_No;
        com.Parameters.Add("@im_no", SqlDbType.NVarChar, 30);
        com.Parameters["@im_no"].Value = Im_No;

        try
        {
            con.Open();
            return decimal.Parse(com.ExecuteScalar().ToString());
        }
        catch
        {
            return 0;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }
    }

    /// <summary>
    /// 确认或取消确认时改变合同状态(发货完否)和从表中商品未发货数量
    /// </summary>
    /// <param name="Cm_No">合同订单号</param>
    /// <param name="Dm_No">发货单号</param>
    /// <param name="Im_No">该合同下所有商品的ID数组</param>
    /// <param name="Qty">该合同下所有商品发货数量数组</param>
    /// <param name="Type">类型(0确认, 1取消确认)</param>
    /// <param name="Auditor">操作员</param>
    /// <returns>操作是否成功</returns>
    public Boolean UpdateSellContractChildRsSpareQty(string Cm_No, string Dm_No, string[] Im_No, decimal[] Qty, int Type, string Auditor)
    {
        Boolean Is_Succeed = false;
        SqlConnection con = new SqlConnection(strSqlConnectionString);

        try
        {
            con.Open();

            string strSql0 = "", strSql1 = "", strSql2 = "", strSql3 = "", strSql4 = "";

            if (Type == 0)
            {
                //发货改为确认状态
                strSql0 = "update tboDueM set is_audit=1, auditor='" + Auditor + "', audit_date=getDate() where dm_no='" + Dm_No + "' and m_no='" + Cm_No + "'";

                //出货确认主从表记录增加(自动生成出货单号)
                 ClsMenuDAL cls_Menu = new ClsMenuDAL();
                string strCode = cls_Menu.GetSystemCode("0", "00", "", 7);
                if (strCode != "Error")
                {
                    strSql1 = "insert into tboDueOutM (out_no, dm_no) values ('" + strCode + "', '" + Dm_No + "')";
                    strSql2 = "insert into tboDueOutD (out_no, dm_no, im_no, qty) select '" + strCode + "', dm_no, im_no, qty from tboDueD where dm_no='" + Dm_No + "'";
                }
                else
                {
                    return false;
                }

                //如果该合同下的所有商品剩余的发货数量为0则修改合同状态为已发货完
                Boolean is_ExcessByD = true;
                for (int i = 0; i < Qty.Length; i++)
                {
                    if (Qty[i] != 0) is_ExcessByD = false;
                }

                if (is_ExcessByD)
                {
                    strSql3 = "update tboContractM set is_fulfill=1 where cm_no='" + Cm_No + "'";
                }
                else
                {
                    strSql3 = "update tboContractM set is_fulfill=0 where cm_no='" + Cm_No + "'";
                }
            }
            else
            {
                //发货改为取消确认状态
                strSql0 = "update tboDueM set is_audit=0, auditor='', audit_date=null where dm_no='" + Dm_No + "' and m_no='" + Cm_No + "'";

                //作废出货确认主从表记录
                ClsMenuDAL cls_Menu = new ClsMenuDAL();//取消系统编码
                string strstrCode = cls_Menu.GetCancelSysCode("select out_no from tboDueOutM where dm_no='" + Dm_No + "'");
                cls_Menu.GetSystemCode("1", "00", strstrCode, 7);
                strSql1 = "delete tboDueOutM where dm_no='" + Dm_No + "'";
                strSql2 = "delete tboDueOutD where dm_no='" + Dm_No + "'";

                //合同改为未发货完状态
                strSql3 = "update tboContractM set is_fulfill=0 where cm_no='" + Cm_No + "'";
            }

            SqlCommand com0 = new SqlCommand(strSql0, con);
            SqlCommand com1 = new SqlCommand(strSql1, con);
            SqlCommand com2 = new SqlCommand(strSql2, con);
            SqlCommand com3 = new SqlCommand(strSql3, con);
            SqlTransaction tran = con.BeginTransaction();//事务开始

            com0.Transaction = tran;
            com1.Transaction = tran;
            com2.Transaction = tran;
            com3.Transaction = tran;

            try
            {
                com0.ExecuteNonQuery();
                com1.ExecuteNonQuery();
                com2.ExecuteNonQuery();
                com3.ExecuteNonQuery();

                tran.Commit();//提交事务
                Is_Succeed = true;
            }
            catch
            {
                tran.Rollback();//回滚事务
                Is_Succeed = false;
            }

            try
            {
                int intImCount = Im_No.Length;
                for (int i = 0; i < intImCount; i++)
                {
                    if (Type == 0)
                    {
                        //未发货数量值减
                        strSql4 = "update tboContractD set qty1=qty1 - " + Qty[i] + ", qty2=qty2 + " + Qty[i] + " where cm_no='" + Cm_No + "' and im_no='" + Im_No[i] + "'";
                    }
                    else
                    {
                        //未发货数量值加
                        strSql4 = "update tboContractD set qty1=qty1 + " + Qty[i] + ", qty2=qty2 - " + Qty[i] + " where cm_no='" + Cm_No + "' and im_no='" + Im_No[i] + "'";
                    }

                    SqlCommand com4 = new SqlCommand(strSql4, con);

                    com4.ExecuteNonQuery();
                }

                Is_Succeed = true;
            }
            catch
            {
                Is_Succeed = false;
            }
        }
        catch
        {
            Is_Succeed = false;
        }
        finally
        {
            if (con.State == ConnectionState.Open)
            {
                con.Close();
            }
        }

        return Is_Succeed;
    }
}
